﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using hotel.Win.Common;

namespace hotel.Win.Report.Excel
{
    public class ExcelReport
    {
        /// <summary>
        /// 酒店总表  
        /// </summary>
        /// <param name="rq"></param>
        public static void ImportZongBiao(DateTime rq)
        {
            var hssfworkbook = NpoiHelper.CreateWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //列大小
            sheet1.SetColumnWidth(0, 16 * 256);
            sheet1.SetColumnWidth(1, 14 * 256);
            sheet1.SetColumnWidth(2, 14 * 256);
            sheet1.SetColumnWidth(3, 14 * 256);
            sheet1.SetColumnWidth(4, 14 * 256);
            //标题
            int rowIndex = 0;
            IRow row = sheet1.CreateRow(rowIndex);
            row.HeightInPoints = 30;
            ICell cell = row.CreateCell(0);            
            cell.SetCellValue(XTCSModel.GetXTCS.JDMC + "总表");
            ICellStyle tstyle = hssfworkbook.CreateCellStyle();
            tstyle.WrapText = true;
            tstyle.Alignment = HorizontalAlignment.CENTER;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 22 * 20;
            font.Boldweight = (short)FontBoldWeight.BOLD;
            font.FontName = "宋体";
            tstyle.SetFont(font);
            cell.CellStyle = tstyle;
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 4));
           
            //第一列样式
            ICellStyle c1style = hssfworkbook.CreateCellStyle();
            c1style.WrapText = true;
            c1style.Alignment = HorizontalAlignment.RIGHT;
            IFont c1font = hssfworkbook.CreateFont();
            c1font.FontHeight = 12 * 20;
            c1font.Boldweight = (short)FontBoldWeight.BOLD;
            c1font.FontName = "宋体";
            c1style.SetFont(c1font);
            //日期
            rowIndex++;
            row = sheet1.CreateRow(rowIndex);
            cell = row.CreateCell(0);
            cell.SetCellValue("打印日期");
            cell.CellStyle = c1style;
            cell = row.CreateCell(1);
            cell.SetCellValue(rq.ToString("yyyy-MM-dd"));
            cell.CellStyle = c1style;
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
            cell = row.CreateCell(3);
            cell.SetCellValue("打印人员");
            cell.CellStyle = c1style;
            //居中样式
            ICellStyle centerStyle = hssfworkbook.CreateCellStyle();
            centerStyle.WrapText = true;
            centerStyle.Alignment = HorizontalAlignment.CENTER;
            IFont rfont = hssfworkbook.CreateFont();
            rfont.FontHeight = 12 * 20;
            rfont.FontName = "宋体";
            c1style.SetFont(rfont);
            //标题列
            rowIndex++;
            row = sheet1.CreateRow(rowIndex);
            cell = row.CreateCell(1);
            cell.CellStyle =centerStyle;
            cell.SetCellValue("今日出租");
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
            cell = row.CreateCell(3);
            cell.CellStyle = centerStyle;
            cell.SetCellValue("本月累计出租");
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 4));
            rowIndex++;
            row = sheet1.CreateRow(rowIndex);
            cell = row.CreateCell(1);
            cell.CellStyle = centerStyle;
            cell.SetCellValue("房数");            
            cell = row.CreateCell(2);
            cell.CellStyle = centerStyle;
            cell.SetCellValue("%");
            cell = row.CreateCell(3);
            cell.CellStyle = centerStyle;
            cell.SetCellValue("房数");
            cell = row.CreateCell(4);
            cell.CellStyle = centerStyle;
            cell.SetCellValue("%");
            
            var _context = MyDataContext.GetDataContext;
            //房类
            var query = from rz in _context.RZJL
                        join r in _context.ROOM on rz.ROOM.ID equals r.ID
                        join t in _context.ROOM_TYPE on r.ROOM_TYPE.ID equals t.ID
                        where rz.CJRQ >= rq
                        group t by t.MC into g
                        select new
                        {
                            Name = g.Key,
                            Count = g.Count(),
                        };
            int iSum = 0;
            int cindex = 0;
            foreach (var item in query)
            {
                rowIndex++;
                cindex++;
                row = sheet1.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.CellStyle = c1style;
                cell.SetCellValue(item.Name);
                cell = row.CreateCell(1);
                cell.CellStyle = centerStyle;
                cell.SetCellValue(item.Count.ToString());
                iSum += item.Count;
            }
            for (int i = 0; i < cindex; i++)
            {
                row = sheet1.GetRow(rowIndex - i);
                cell = row.CreateCell(2);                
                cell.CellStyle = centerStyle;
                cell.SetCellValue((Math.Round(Convert.ToDecimal(row.GetCell(1).StringCellValue) / iSum, 2) * 100).ToString());
            }
            //客人类别
            query = from rz in _context.RZJL
                        where rz.CJRQ >= rq
                        group rz by rz.KRLB into g
                        select new
                        {
                            Name = g.Key,
                            Count = g.Count(),
                        };
            iSum = 0;
            cindex = 0;
            foreach (var item in query)
            {
                rowIndex++;
                cindex++;
                row = sheet1.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.CellStyle = c1style;
                cell.SetCellValue(item.Name);
                cell = row.CreateCell(1);
                cell.CellStyle = centerStyle;
                cell.SetCellValue(item.Count.ToString());
                iSum += item.Count;
            }
            for (int i = 0; i < cindex; i++)
            {
                row = sheet1.GetRow(rowIndex - i);
                cell = row.CreateCell(2);
                cell.CellStyle = centerStyle;
                cell.SetCellValue((Math.Round(Convert.ToDecimal(row.GetCell(1).StringCellValue) / iSum, 2) * 100).ToString());
            }
            NpoiHelper.WriteToFile(hssfworkbook);

        }
    }
}
